---
sidebar_label: Data validations
sidebar_position: 10
description: Data validations in Hasura over Postgres
keywords:
  - hasura
  - docs
  - postgres
  - schema
  - data validation
---

import GraphiQLIDE from '@site/src/components/GraphiQLIDE';
import Thumbnail from '@site/src/components/Thumbnail';
import Tabs from '@theme/Tabs';
import TabItem from '@theme/TabItem';

# Postgres: Data Validations

## Introduction

Many times, we need to perform validations of input data before inserting or updating objects.

The best solution to implement a validation depends on the complexity of the validation logic and the layer where you
would like to add it.

- If you would like the validation logic to be a part of your database schema, Postgres check constraints or triggers
  would be ideal solutions to add your validation.
- If you would like the validation logic to be at the GraphQL API layer, Hasura permissions can be used to add your
  validation.
- If the validation logic requires complex business logic and/or needs information from external sources, you can use
  Hasura Actions to perform your validation.

These solutions are explained in some more detail below.

## Using Postgres check constraints {#pg-data-validations-check-constraints}

If the validation logic can be expressed by using only static values and the columns of the table, you can use
[Postgres check constraints](https://www.postgresql.org/docs/current/ddl-constraints.html).

**Example:** Check that the `rating` for an author is between 1 and 10 only.

Let's say we have the following table in our schema:

```sql
author (id uuid, name text, rating integer)
```

We can now add a check constraint to limit the `rating` values as follows:

<Tabs groupId="user-preference" className="api-tabs">
<TabItem value="console" label="Console">

Head to the `Modify` tab in the table page and add a check constraint in the `Check Constraints` section:

<Thumbnail src="/img/schema/validation-add-check-constraint.png" alt="Add check constraint" />

</TabItem>
<TabItem value="cli" label="CLI">

[Create a migration manually](/migrations-metadata-seeds/manage-migrations.mdx#create-manual-migrations) and add the
following SQL statement to the `up.sql` file:

```sql
ALTER TABLE author
ADD CONSTRAINT authors_rating_check CHECK (rating > 0 AND rating <= 10);
```

Add the following statement to the `down.sql` file in case you need to
[roll back](/migrations-metadata-seeds/manage-migrations.mdx#roll-back-migrations) the above statement:

```sql
ALTER TABLE author DROP CONSTRAINT authors_rating_check;
```

Apply the migration by running:

```bash
hasura migrate apply
```

</TabItem>
<TabItem value="api" label="API">

You can add a check constraint by using the [run_sql](/api-reference/schema-api/run-sql.mdx#schema-run-sql) schema API:

```http
POST /v2/query HTTP/1.1
Content-Type: application/json
X-Hasura-Role: admin

{
  "type": "run_sql",
  "args": {
    "source": "<db_name>",
    "sql": "ALTER TABLE author ADD CONSTRAINT authors_rating_check CHECK (rating > 0 AND rating <= 10);"
  }
}
```

</TabItem>
</Tabs>

If someone now tries to add an author with a rating of `11`, the following error is thrown:

<GraphiQLIDE
  query={`mutation {
  insert_author(
    objects: {
      name: "Enid Blyton",
      rating: 11
    }) {
      affected_rows
    }
}`}
  response={`{
  "errors": [
    {
      "message": "Check constraint violation. new row for relation \"author\" violates check constraint \"authors_rating_check\",
      "extensions": {
        "path": "$.selectionSet.insert_author.args.objects",
        "code": "permission-error"
      }
    }
  ]
}`}
/>

Learn more about [Postgres check constraints](https://www.postgresql.org/docs/current/ddl-constraints.html).

## Using Postgres triggers {#pg-data-validations-pg-triggers}

If the validation logic is more complex and requires the use of data from other tables and/or functions, then you can
use [Postgres triggers](https://www.postgresql.org/docs/current/sql-createtrigger.html).

**Example:** Validate that an article's `content` does not exceed a certain number of words.

Suppose we have the following table in our schema:

```sql
article (id uuid, title text, content text)
```

We can now create a [Postgres function](https://www.postgresql.org/docs/current/sql-createfunction.html) that checks if
an article's content exceeds a certain number of words, and then add a
[Postgres trigger](https://www.postgresql.org/docs/current/sql-createtrigger.html) that will call this function every
time before an article is inserted or updated.

<Tabs groupId="user-preference" className="api-tabs">
<TabItem value="console" label="Console">

- Head to the `Data -> SQL` section of the Hasura Console
- Enter the SQL statement below to create a Postgres function and trigger
- Hit the `Run` button

</TabItem>
<TabItem value="cli" label="CLI">

[Create a migration manually](/migrations-metadata-seeds/manage-migrations.mdx#create-manual-migrations) and add the SQL
statement below to create a Postgres function and trigger to the `up.sql` file. Also, add an SQL statement to the
`down.sql` to revert the previous statement in case you need to
[roll back](/migrations-metadata-seeds/manage-migrations.mdx#roll-back-migrations) the migration.

Apply the migration by running:

```bash
hasura migrate apply
```

</TabItem>
<TabItem value="api" label="API">

You can add a Postgres function and trigger by using the [run_sql](/api-reference/schema-api/run-sql.mdx#schema-run-sql)
schema API:

```http
POST /v2/query HTTP/1.1
Content-Type: application/json
X-Hasura-Role: admin

{
  "type": "run_sql",
  "args": {
    "source": "<db_name>",
    "sql": "<SQL statement below>"
  }
}
```

</TabItem>
</Tabs>

```plsql
CREATE FUNCTION check_content_length()
RETURNS trigger AS $$
DECLARE content_length INTEGER;
BEGIN
  -- split article content into words and get count
  select array_length(regexp_split_to_array(NEW.content, '\s'),1) INTO content_length;

  -- throw an error if article content is too long
  IF content_length > 100 THEN
      RAISE EXCEPTION USING ERRCODE= '22000', MESSAGE= 'Content can not have more than 100 words';
  END IF;

  -- return the article row if no error
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER check_content_length_trigger
  BEFORE INSERT OR UPDATE ON "article"
  FOR EACH ROW
  EXECUTE PROCEDURE check_content_length();
```

Now, if we try to insert an article whose content has more than 100 words, we'll receive the following error:

<GraphiQLIDE
  query={`mutation {
  insert_article(
    objects: {
      title: "lorem ipsum"
      content: "Lorem ipsum dolor sit amet, consectetur adipiscing elit. Aenean et nisl dolor. Nulla eleifend odio et velit aliquet, sed convallis quam bibendum. Cras consequat elit quis est vehicula, nec dignissim dolor cursus. Phasellus suscipit magna ac turpis pulvinar ultricies. Nulla sed lacus sed metus egestas scelerisque nec sed urna. Fusce lorem velit, efficitur sed luctus in, fringilla ac urna. Maecenas fermentum augue sit amet malesuada imperdiet. Suspendisse mattis dignissim quam, at tempor dui tincidunt sed. Maecenas placerat erat nec erat aliquet rutrum. Mauris congue velit nec ultrices dapibus. Duis aliquam, est ac ultricies viverra, ante augue dignissim massa, quis iaculis ex dui in ex. Curabitur pharetra neque ac nisl fringilla, vel pellentesque orci molestie.",
    }
  ) {
    affected_rows
  }
}`}
  response={`{
  "errors": [
    {
      "message": "postgres query error",
      "extensions": {
        "path": "$",
        "code": "data-exception"
      },
      "message": "Content can not have more than 100 words"
    }
  ]
}`}
/>

Learn more about [Postgres triggers](https://www.postgresql.org/docs/current/sql-createtrigger.html).

## Using Hasura permissions

Hasura permissions provides two different ways to validate data:

1. If input arguments of a mutations needs to be validated, you can use the
   [input validation](/docs/schema/postgres/input-validations.mdx) feature. This
   allows you to write custom validation logic that is run in an external webhook before hitting the
   database to execute the mutation.
2. If the validation logic can be expressed **declaratively** using static
   values and data from the database, then you can use [row level
   permissions](/auth/authorization/permissions/row-level-permissions.mdx) to
   perform the validations. (Read more
   about[Authorization](/auth/authorization/index.mdx)).

**Example 1:** Validate that a valid email is being inserted

Suppose, we have the following table in our schema:
```sql
customer (id uuid, name text, city text, email text)
```
Now, we can create a role `user` and add an input validation rule as follows:


<Tabs className="api-tabs">
<TabItem value="console" label="Console">

<Thumbnail
  src="/img/schema/input-validation-create-permission.png"
  alt="Using boolean expressions to build rules"
  width="500px"
/>

</TabItem>
<TabItem value="cli" label="CLI">

You can define the input validation  in the `metadata -> databases -> [database-name] -> tables -> [table-name].yaml`
file, eg:

```yaml {9-14}
- table:
    schema: public
    name: customer
  insert_permissions:
    - role: user
      permission:
        columns: []
        filter: {}
        validate_input:
          type: http
          definition:
            url: http://www.somedomain.com/validateCustomerMutation
            forward_client_headers: true
            timeout: 5
        
```

Apply the Metadata by running:

```bash
hasura metadata apply
```

</TabItem>
<TabItem value="api" label="API">

You can define the input validations when using the [permissions Metadata API](/api-reference/metadata-api/permission.mdx).
Example with a Postgres DB:

```http {14-21}
POST /v1/metadata HTTP/1.1
Content-Type: application/json
X-Hasura-Role: admin

{
  "type": "pg_create_insert_permission",
  "args": {
    "source": "<db_name>",
    "table": "customer",
    "role": "user",
    "permission": {
      "columns": "*",
      "filter": {},
      "validate_input": {
        "type": "http",
        "definition": {
          "forward_client_headers": true,
          "headers": [],
          "timeout": 5,
          "url": "http://www.somedomain.com/validateCustomerMutation"
        }
      }
    }
  }
}
```

</TabItem>
</Tabs>

If we try to insert a customer with an invalid email, we will get a `validation-failed` error:

<GraphiQLIDE
  query={`mutation {
  insert_customer(
    objects: {
      name: "customer 1"
      email: "random-email",
    }
  ) {
    affected_rows
  }
}`}
  response={`{
  "errors": [
    {
      "message": "customer email id is not valid",
      "extensions": {
        "path": "$",
        "code": "validation-failed"
      }
    }
  ]
}`}
/>

**Example 2:** Validate that an `article` can be inserted only if `title` is not empty.

Suppose, we have the following table in our schema:

```sql
article (id uuid, title text, content text, author_id uuid)
```

Now, we can create a role `user` and add an insert validation rule as follows:

<Tabs groupId="user-preference" className="api-tabs">
<TabItem value="console" label="Console">

<Thumbnail src="/img/schema/validation-not-empty.png" alt="validation using permission: title cannot be empty" />

</TabItem>
<TabItem value="cli" label="CLI">

You can add roles and permissions in the `tables.yaml` file inside the `metadata` directory:

```yaml {4-9}
- table:
    schema: public
    name: article
  insert_permissions:
    - role: user
      permission:
        check:
          title:
            _ne: ''
```

Apply the Metadata by running:

```bash
hasura metadata apply
```

</TabItem>
<TabItem value="api" label="API">

You can add an insert permission rule by using the
[pg_create_insert_permission](/api-reference/metadata-api/permission.mdx#metadata-pg-create-insert-permission) metadata
API:

```http
POST /v1/metadata HTTP/1.1
Content-Type: application/json
X-Hasura-Role: admin

{
  "type": "pg_create_insert_permission",
  "args": {
    "source": "<db_name>",
    "table": "article",
    "role": "user",
    "permission": {
      "check": {
        "title": {
          "_ne": ""
        }
      }
    }
  }
}
```

</TabItem>
</Tabs>

If we try to insert an article with `title = ""`, we will get a `permission-error`:

<GraphiQLIDE
  query={`mutation {
  insert_article(
    objects: {
      title: ""
      content: "Lorem ipsum dolor sit amet",
    }
  ) {
    affected_rows
  }
}`}
  response={`{
  "errors": [
    {
      "message": "check constraint of an insert/update permission has failed",
      "extensions": {
        "path": "$.selectionSet.insert_article.args.objects",
        "code": "permission-error"
      }
    }
  ]
}`}
/>

**Example 3:** Validate that an `article` can be inserted only if its `author` is active.

Suppose, we have 2 tables:

```sql
author (id uuid, name text, is_active boolean)
article (id uuid, author_id uuid, content text)
```

Also, suppose there is an [object relationship](/schema/postgres/table-relationships/index.mdx#pg-graphql-relationships)
`article.author` defined as:

```sql
article.author_id -> author.id
```

Now, we can create a role `user` and add an insert validation rule as follows:

<Tabs groupId="user-preference" className="api-tabs">
<TabItem value="console" label="Console">

<Thumbnail
  src="/img/schema/validation-author-isactive.png"
  alt="validation using permissions: author should be active"
/>

</TabItem>
<TabItem value="cli" label="CLI">

You can add roles and permissions in the `tables.yaml` file inside the `metadata` directory:

```yaml {4-10}
- table:
    schema: public
    name: article
  insert_permissions:
    - role: user
      permission:
        check:
          author:
            is_active:
              _eq: true
```

Apply the Metadata by running:

```bash
hasura metadata apply
```

</TabItem>
<TabItem value="api" label="API">

You can add an insert permission rule by using the
[pg_create_insert_permission](/api-reference/metadata-api/permission.mdx#metadata-pg-create-insert-permission) metadata
API:

```http
POST /v1/metadata HTTP/1.1
Content-Type: application/json
X-Hasura-Role: admin

{
  "type": "pg_create_insert_permission",
  "args": {
    "source": "<db_name>",
    "table": "article",
    "role": "user",
    "permission": {
      "check": {
        "author": {
          "is_active": true
        }
      }
    }
  }
}
```

</TabItem>
</Tabs>

If we try to insert an article for an author for whom `is_active = false`, we will receive a `permission-error` :

<GraphiQLIDE
  query={`mutation {
  insert_article(
    objects: {
      title: "lorem ipsum"
      content: "Lorem ipsum dolor sit amet, consectetur adipiscing elit.",
      author_id: 2
    }
  ) {
    affected_rows
  }
}`}
  response={`{
  "errors": [
    {
      "message": "check constraint of an insert/update permission has failed",
      "extensions": {
        "path": "$.selectionSet.insert_article.args.objects",
        "code": "permission-error"
      }
    }
  ]
}`}
/>

:::info Note

Permissions are scoped to a user's role. So, if a validation check needs to be global then you will have to define it
for all roles which have insert/update permissions.

A few features on the roadmap should simplify this experience in the future.

:::

## Using Hasura Actions

If the validation requires complex custom business logic and/or needs information from external sources, you can use
[Actions](/actions/overview.mdx) to perform your validation.

**Example:** Check with an external service that an author's name is not deny-listed before inserting them.

Let's assume we have an external service that stores and manages deny-listed authors. Before inserting an author we need
to check with this service if they are deny-listed or not.

The validation process looks as follows:

<Thumbnail
  src="/img/schema/diagram-actions-data-validation.png"
  alt="validation using actions: article not deny-listed"
  width="60%"
/>

Actions allow us to define [custom types](/actions/types.mdx) in our GraphQL schema.

We can [create a new action](/actions/create.mdx) called `InsertAuthor` that takes an `author` object with type
`AuthorInput` as input and returns an object of type `AuthorOutput`.

```graphql
type Mutation {
  InsertAuthor(author: AuthorInput!): AuthorOutput
}

input AuthorInput {
  name: String!
  rating: Int!
  is_active: Boolean!
}

type AuthorOutput {
  id: Int!
}
```

The business logic of an action - in our case the author validation - happens in the
[action handler](/actions/action-handlers.mdx) which is an HTTP webhook which contains the code to call the external
service.

The following is a sample code that could be added to the event handler to implement the data validation:

```javascript
function getDenylistedAuthorsFromApi() {
  // make external api call & return deny-listed authors list
}

function insertAuthorViaHasura() {
  // run insert_author mutation & return response
}

const denylistedAuthors = getDenylistedAuthorsFromApi();

if (denylistedAuthors.includes(author.name)) {
  return res.status(400).json({ message: 'Author is deny-listed' });
} else {
  const insertAuthorResponse = insertAuthorViaHasura();

  return res.json(insertAuthorResponse);
}
```

When we now insert an author, our action handler will be called and it will check if the author is deny-listed. If it's
not, the author will be inserted and the `id` will be returned. If the author is deny-listed, we get the following error
message:

<GraphiQLIDE
  query={`mutation insertArticle {
  InsertAuthor(author: { name: "Thanos" }) {
    id
  }
}`}
  response={`{
  "errors": [
    {
      "extensions": {
        "path": "$",
        "code": "unexpected"
      },
      "message": "Author is deny-listed"
    }
  ]
}`}
/>

:::info Note

For actual examples of data validations with actions, refer to the
[actions examples repo](https://github.com/hasura/hasura-actions-examples/tree/master/data-validations).

:::
